Course: Tableau Training
Topic: Optimizing Data Extracts and Connections
Learning Objective: Understand Tableau data extracts, live connections, extract creation, refresh strategies, connection optimization, data reduction techniques, and practical methods for improving workbook performance.
Register for a Tableau Course Demo
Tableau connects to data from databases, spreadsheets, text files, cloud applications, and other supported sources. The way Tableau accesses and stores this data can significantly affect dashboard performance, data freshness, refresh time, and resource usage.
Two important concepts are data extracts and live connections. A live connection queries the underlying data source as needed, while an extract stores a snapshot of selected data that Tableau can query independently of the original source during analysis.
Optimizing extracts and connections means choosing the appropriate connection type, reducing unnecessary data, improving query efficiency, managing refreshes, and maintaining the accuracy of analytical results.
A retail company stores five years of transaction records in a database. Its management dashboard only needs monthly sales, profit, and regional performance. Retrieving every transaction repeatedly may be unnecessary. Depending on the reporting requirements, a filtered or aggregated extract may reduce processing and improve the dashboard's responsiveness.
A Tableau data extract is a stored snapshot of selected data that Tableau can use for analysis. Extracts commonly use Tableau's Hyper data engine and are stored in the Hyper format, with the file extension .hyper.
When a workbook uses an extract, Tableau can query the extracted data instead of requesting every analytical result directly from the original database. This can improve performance, especially when the original source is slow or repeatedly queried.
A company creates a sales extract containing order date, region, product category, sales, and profit. Tableau can use these stored fields to create charts and dashboards without querying the original database for every interaction.
Reference: Official Tableau Documentation: Extract Your Data.
A live connection allows Tableau to query the underlying data source as required. The database or other connected system processes the requests, and Tableau uses the returned results to display visualizations.
Live connections are useful when reports require current source data or when the underlying database is designed to handle analytical queries efficiently.
A logistics company monitors the latest delivery status from an operational database. If the report requires up-to-date information, a live connection may be more suitable than a scheduled extract that becomes outdated between refreshes.
Important: A live connection does not guarantee that every change appears instantly. Caching, database transactions, connector behavior, and application configuration can affect what users see.
| Feature | Data Extract | Live Connection |
|---|---|---|
| Data storage | Stores a snapshot of selected data. | Uses the underlying source directly for queries. |
| Data freshness | Depends on the most recent successful refresh. | Can reflect current source data, subject to caching and source behavior. |
| Performance | Can improve analytical response times. | Depends on source query performance and network conditions. |
| Database workload | Can reduce repeated query traffic after the extract is created. | Queries can add workload to the original source. |
| Refresh requirements | Requires refreshes when the snapshot must be updated. | No extract refresh is required. |
| Storage | Requires space for the extract. | Does not require a separate extract for the connection. |
| Offline analysis | Possible when the required local extract is available. | Generally requires access to the live source. |
| Suitable use case | Scheduled reporting and repeated analysis with acceptable data latency. | Reports requiring current data from a suitable source. |
Best practice: Do not assume that extracts are always faster or that live connections are always slower. Test both options where practical.
Creating an extract allows Tableau to store a snapshot of the data for analysis.
| Setting | Purpose |
|---|---|
| Filters | Restrict which records are included in the extract. |
| Aggregation | Store data at an appropriate summarized level when supported and suitable. |
| Number of Rows | Choose all rows, a sample, or a top-N subset where the interface and data source support it. |
| Incremental Refresh | Configure a supported refresh that adds new rows according to a selected field and refresh strategy. |
| Data Storage | Choose logical-table or physical-table storage options where available. |
Warning: A sample or Top N extract is not necessarily suitable for a complete business report. Use these options only when the reduced data scope is appropriate.
Extract size affects storage requirements and may affect extract creation, refresh time, and query performance. The goal is to retain the data necessary for the required analysis without storing unnecessary fields or records.
Identify columns that are not used in the workbook, calculations, filters, or required data model. Where appropriate, use Tableau's Hide All Unused Fields option before creating or recreating an extract.
Hidden fields are excluded when the extract is created, which can reduce its size. Verify field dependencies before hiding or removing fields.
Use extract filters to limit the data to the relevant reporting period, region, business unit, or other required criteria.
Example: A dashboard for the current financial year may not require ten years of historical transaction data in its extract, unless historical comparisons are also required.
When the report needs summary-level analysis, consider aggregating the extract to the appropriate level of detail. For example, a management dashboard might use monthly sales by region instead of every transaction.
Aggregation reduces detail and may improve performance, but it can prevent transaction-level analysis and change the results of calculations that depend on individual records.
Fields containing a large number of unique values, such as transaction identifiers, may increase data volume and complicate certain analyses. Exclude them from the extract when they are not needed.
Limit date ranges when older data is not required. If historical trends or year-over-year comparisons are important, retain enough history to support those requirements.
Refresh strategies determine how an extract is updated with new source data.
A full refresh replaces the existing extract contents with data retrieved from the source. It is useful when the extract must reflect source changes comprehensively or when the source data and refresh configuration require a complete reload.
Advantages:
Disadvantages:
An incremental refresh updates an existing extract by retrieving additional rows according to a configured field and refresh strategy. It is useful when new data is added regularly and the selected field reliably identifies the new rows.
Advantages:
Limitations:
| Feature | Full Refresh | Incremental Refresh |
|---|---|---|
| Update method | Replaces extract contents with refreshed source data. | Updates the extract with qualifying rows according to the configured strategy. |
| Typical workload | Reloads the required dataset. | Processes qualifying new rows. |
| Refresh duration | Can be longer for large datasets. | Can be shorter when relatively few new rows need processing. |
| Historical updates | Can capture source changes included in the full reload. | May miss changes outside the incremental selection logic. |
| Deletions | Can reflect source deletions in the refreshed result. | Do not assume source deletions are automatically removed. |
A company adds 10,000 sales records daily to a database containing 20 million records. An incremental refresh may be more efficient than reloading all 20 million records every day, provided the chosen incremental field and data pipeline reliably identify new records.
Reference: Tableau Extracts and Refresh Options.
Live connections rely on the performance of the underlying data source and the queries Tableau sends to it. Optimizing a live connection therefore involves both Tableau workbook design and the source system.
For supported database sources, a native database driver can provide better performance or compatibility than a generic driver. Use a supported and appropriately maintained driver for the database and Tableau version.
Investigate slow queries using database tools and query execution plans. Review joins, filtering conditions, aggregation, indexes where appropriate, and the amount of data scanned.
Use relevant filters, remove unused fields from the workbook design, and avoid requesting more detail than the visualization requires.
Custom SQL can be useful, but it may limit Tableau's ability to generate or optimize queries for a particular data source. Use it when necessary and review its execution plan. An extract may be useful when the custom query can be run during refresh instead of repeatedly for interactive analysis.
Slow connections may be caused by network latency, DNS resolution, authentication, proxies, source availability, or database workload. Compare performance under controlled conditions to distinguish source-level delays from environmental issues.
Review relationships and joins to avoid unintended duplication, excessive data volume, and unnecessary query complexity. Choose the model that preserves the correct level of detail and aggregation.
Some calculations can be computed in advance and stored in an extract. Tableau provides the Compute Calculations Now option for materializing supported calculations in an extract. Depending on the calculations and workload, this may speed up future queries.
Reference: Tableau: Test Your Data and Use Extracts.
Tableau's data model distinguishes logical tables from physical tables. Logical tables define relationships at the model level, while physical tables define joins and unions within a logical table.
When configuring extract storage, Tableau can offer logical-table and physical-table options. The correct choice depends on the data model and which extract features are required.
| Aspect | Logical Tables | Physical Tables |
|---|---|---|
| Data model | Preserves the logical relationship structure. | Uses the physical table structure for extract storage and query behavior. |
| Joins | Joins within a logical table are applied when the extract is created. | Eligible physical joins can be performed at query time. |
| Extract features | Supports many extract features, including filters and aggregation where applicable. | Some extract reduction and refresh features may be unavailable. |
| Use case | Often the recommended default for general extract workflows. | May be useful in specific data models and eligible join scenarios. |
Best practice: Use the default logical-table storage option unless there is a clear, tested reason to use physical-table storage. Check the Tableau documentation for feature limitations before changing the setting.
Published extracts on Tableau Server or Tableau Cloud can be refreshed on a schedule, depending on the platform, permissions, connection type, and configuration.
A daily management dashboard uses sales data that is finalized overnight. A scheduled early-morning extract refresh may be sufficient. An operational dashboard that requires frequent changes may need a live connection or a more frequent refresh schedule, depending on the source and platform capabilities.
Reference: Tableau Cloud: Optimize Bridge Refresh Performance.
Tableau workbooks can use embedded data sources stored with the workbook or connect to published data sources managed on Tableau Server or Tableau Cloud.
| Feature | Embedded Data Source | Published Data Source |
|---|---|---|
| Location | Stored within the workbook. | Published and managed separately. |
| Reuse | Usually tied to the workbook in which it is embedded. | Can be reused by multiple workbooks when access is permitted. |
| Management | Often managed with the workbook. | Can be centrally maintained by authorized owners or administrators. |
| Consistency | Different workbooks may maintain separate copies. | Can help standardize data definitions and connection management. |
| Refresh planning | Refresh configuration is associated with the workbook and its embedded source. | Refresh can be managed centrally for the published source, subject to platform settings. |
A published data source can help reduce duplication and simplify governance when multiple workbooks need the same data. However, the best approach depends on the refresh workload, data access needs, ownership, and sharing requirements.
| Problem | Possible Cause | Recommended Solution |
|---|---|---|
| Extract takes too long to create | Large dataset, excessive fields, or complex source query. | Reduce unnecessary data and investigate source query performance. |
| Extract file is too large | Too many rows, unused columns, or unnecessary detail. | Review extract filters, aggregation, and unused fields. |
| Dashboard is slow with a live connection | Slow queries, network delays, or database resource constraints. | Use Performance Recording and investigate the relevant source or network issue. |
| Incremental refresh misses updates | Historical rows changed or the incremental field does not capture all changes. | Review the incremental strategy and perform a full refresh when required. |
| Refresh fails on Tableau Cloud | Credentials, permissions, connectivity, or private-network access issues. | Check refresh logs, authentication, source connectivity, and Bridge configuration where applicable. |
| Extract results differ from live results | Differences in data freshness, collation, data types, calculations, or extract configuration. | Compare the source and extract settings, refresh the data, and validate the results. |
| Changing to an extract does not improve performance | The bottleneck may be rendering, workbook complexity, or another operation. | Record performance and optimize the actual bottleneck. |
| Extract refresh consumes too many resources | Large refresh workload or overlapping scheduled jobs. | Review data scope, refresh strategy, scheduling, and source capacity. |
A company has a Tableau dashboard connected to a database containing 15 million sales records. The dashboard is slow, the extract is large, and the daily refresh takes too long.
| Metric | Before Optimization | After Optimization |
|---|---|---|
| Extract size | Record measured size | Record measured size |
| Dashboard load time | Record measured time | Record measured time |
| Refresh duration | Record measured time | Record measured time |
| Data freshness | Record last successful refresh | Confirm required freshness |
| Data accuracy | Validate baseline totals | Revalidate totals and calculations |
Learning point: These are measurement fields, not promised results. A successful optimization should improve the relevant performance measure while preserving the required data and business logic.
Answer: A data extract is a stored snapshot of selected source data that Tableau can query for analysis without repeatedly querying the original source for every analytical request.
Answer: A live connection allows Tableau to query the underlying data source as needed. Its performance depends on source capability, query design, network conditions, and system load.
Answer: A live connection queries the original source, while an extract uses a stored snapshot that must be refreshed to reflect subsequent source changes.
Answer: Remove unused fields, filter unnecessary records, limit date ranges where appropriate, and use aggregation when the required analysis supports it.
Answer: A full refresh replaces the existing extract contents with data retrieved from the source during the refresh.
Answer: An incremental refresh updates an extract with qualifying new rows according to a configured field and refresh strategy. Its suitability depends on the source data and how updates are handled.
Answer: Do not assume so. Incremental refreshes generally focus on qualifying new rows, so source deletions and changes to existing records may require additional handling or a full refresh.
Answer: Use a live connection when current source data is important and the source can support the expected query workload.
Answer: Consider an extract when a stored snapshot meets the freshness requirement and can improve analytical performance, reduce repeated source queries, or support offline analysis.
Answer: A suitable, supported database driver can improve connectivity, compatibility, and query performance compared with an unsuitable or outdated driver.
Answer: It materializes supported calculations in an extract so that certain calculation results can be stored in advance, potentially improving future queries.
Answer: The extract may contain too much data, the workbook may have expensive calculations or rendering, the extract may be poorly designed, or another part of the workflow may be the actual bottleneck.
Answer: A published data source is managed on Tableau Server or Tableau Cloud and can be reused by authorized workbooks and users.
Answer: Compare measured dashboard response time, extract size, refresh duration, data freshness, and the accuracy of totals and calculations before and after the change.
Optimizing data extracts and connections is an important part of developing efficient Tableau workbooks. Extracts can improve analytical performance and reduce repeated source queries, while live connections can support reports that need current source data. Neither approach is automatically best for every situation.
The most effective strategy is to understand the reporting requirements, select the appropriate connection type, reduce unnecessary data, optimize queries and calculations, configure refreshes carefully, and measure performance before and after changes. Always confirm that data accuracy, security, and freshness remain appropriate.
Explore the course: JustAcademy Tableau Training
Register for a demo: Register for a Tableau Course Demo
